﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_StockInGoodsInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddG_StockInGoodsInfo(G_StockInGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_StockInGoodsInfo");
            strSql.Append("(");
            strSql.Append("StockInId,");
            strSql.Append("GoodsId,");
            strSql.Append("GoodsName,");
            strSql.Append("GoodsNum");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@StockInId,");
            strSql.Append("@GoodsId,");
            strSql.Append("@GoodsName,");
            strSql.Append("@GoodsNum");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = model.StockInId },
                new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId },
                new SqlParameter("@GoodsName", SqlDbType.NVarChar, 50) { Value = model.GoodsName },
                new SqlParameter("@GoodsNum", SqlDbType.Decimal, 9) { Value = model.GoodsNum }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }


        public List<G_StockInGoodsInfo> GetG_StockInGoodsInfoList(string strWhere)
        {
            List<G_StockInGoodsInfo> list = new List<G_StockInGoodsInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" StockInGoodsId");
            strSql.Append(",StockInId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.AppendFormat(" FROM G_StockInGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockInGoodsId = r.GetOrdinal("StockInGoodsId");
                int StockInId = r.GetOrdinal("StockInId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                G_StockInGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockInGoodsInfo();
                    model.StockInGoodsId = r.GetInt32(StockInGoodsId);
                    model.StockInId = r.GetInt32(StockInId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }



        public List<G_StockInGoodsInfo> GetG_StockInGoodsInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_StockInGoodsInfo> list = new List<G_StockInGoodsInfo>();
            totalCount = GetG_StockInGoodsInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY StockInGoodsId DESC) AS ORDERBYID");
            strSql.Append(",StockInGoodsId");
            strSql.Append(",StockInId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.AppendFormat(" FROM G_StockInGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockInGoodsId = r.GetOrdinal("StockInGoodsId");
                int StockInId = r.GetOrdinal("StockInId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                G_StockInGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockInGoodsInfo();
                    model.StockInGoodsId = r.GetInt32(StockInGoodsId);
                    model.StockInId = r.GetInt32(StockInId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_StockInGoodsInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_StockInGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }



        public G_StockInGoodsInfo GetG_StockInGoodsInfoModel(int stockInGoodsId)
        {
            G_StockInGoodsInfo model = new G_StockInGoodsInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("StockInGoodsId");
            strSql.Append(",StockInId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.Append(" FROM G_StockInGoodsInfo WITH(NOLOCK) WHERE StockInGoodsId=@StockInGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockInGoodsId", SqlDbType.Int, 4) { Value = stockInGoodsId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int StockInGoodsId = r.GetOrdinal("StockInGoodsId");
                int StockInId = r.GetOrdinal("StockInId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                if (r.Read())
                {
                    model.StockInGoodsId = r.GetInt32(StockInGoodsId);
                    model.StockInId = r.GetInt32(StockInId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                }
                r.Close();
            }
            return model;
        }


        /// <summary>
        /// 获取已入库商品明细
        /// </summary>
        /// <param name="PurchaseOrderId"></param>
        /// <returns></returns>
        public List<G_StockInGoodsInfo> GetG_StockInGoodsInfoList(int PurchaseOrderId)
        {
            List<G_StockInGoodsInfo> list = new List<G_StockInGoodsInfo>();
            string strSql = @"
SELECT  dbo.G_StockInGoodsInfo.GoodsId ,
        GoodsNum ,
        AddTime
FROM    G_StockInInfo WITH ( NOLOCK )
        LEFT JOIN G_StockInGoodsInfo WITH ( NOLOCK ) ON dbo.G_StockInInfo.StockInId = dbo.G_StockInGoodsInfo.StockInId
WHERE   [Status] = 0
        AND CheckStatus = 2
        AND PurchaseOrderId = @PurchaseOrderId";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = PurchaseOrderId }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                int AddTime = r.GetOrdinal("AddTime");
                G_StockInGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockInGoodsInfo
                    {
                        GoodsId = r.GetInt32(GoodsId),
                        GoodsNum = r.GetDecimal(GoodsNum),
                        AddTime = r.GetDateTime(AddTime)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }
    }
}
